package com.gome.ocean.common.utils;

import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.alibaba.cobar.parser.ast.expression.Expression;
import com.alibaba.cobar.parser.ast.expression.primary.Identifier;
import com.alibaba.cobar.parser.ast.expression.primary.literal.LiteralNumber;
import com.alibaba.cobar.parser.ast.fragment.ddl.ColumnDefinition;
import com.alibaba.cobar.parser.ast.fragment.ddl.datatype.DataType.DataTypeName;
import com.alibaba.cobar.parser.ast.fragment.ddl.index.IndexDefinition;
import com.alibaba.cobar.parser.ast.stmt.SQLStatement;
import com.alibaba.cobar.parser.ast.stmt.ddl.DDLCreateTableStatement;
import com.alibaba.cobar.parser.util.Pair;

/**
 * DML DDL 语句验证
 * 
 * @author haqiaolong 2016年10月10日 上午10:40:08
 * @since 5.0.0
 */
public final class SqlValidateHelper {

    public static Map<Boolean, List<String>> validate(String sql) {
        Map<Boolean, List<String>> result = new HashMap<Boolean, List<String>>();
        List<String> reason = new ArrayList<String>();
        try {
            ParserResult parserResult = GddlParseHelper.parseSql(sql);
            SQLStatement sqlStatement = GddlParseHelper.parseStatement(sql, parserResult);
            if (sqlStatement instanceof DDLCreateTableStatement) { // 创建表
                DDLCreateTableStatement createTable = (DDLCreateTableStatement) sqlStatement;
                String tableName = createTable.getTable().getIdText();
                if (StringUtils.isNotEmpty(tableName)) {
                    if (tableName.length() >= 30) {
                        reason.add("表名长度不能超过30个字符！tableName : " + tableName);
                    }
                    if (StringUtils.contains(tableName, "_")) {
                        if (!StringUtils.isAllLowerCase(StringUtils.replace(tableName, "_", ""))) {
                            reason.add("表名不能包含大写！tableName : " + tableName);
                        }
                    } else {
                        reason.add("表名必须包含_下划线分隔！tableName : " + tableName);
                    }
                }
                // 列检查
                for (Pair<Identifier, ColumnDefinition> column : createTable.getColDefs()) {
                    String columnName = column.getKey().getIdText();
                    ColumnDefinition columnValue = column.getValue();
                    if (!columnValue.isAutoIncrement()) {
                        // char类型的长度不能超过256
                        if (columnValue.getDataType().getTypeName() == DataTypeName.CHAR) {
                            Expression length = columnValue.getDataType().getLength();
                            if (length instanceof LiteralNumber) {
                                int size = ((LiteralNumber) length).getNumber().intValue();
                                if (size >= 256) {
                                    reason.add("字段值不能超过256！columnName : " + columnName);
                                }
                            }
                        }
                        if (columnValue.getDefaultVal() == null) {
                            if (columnValue.getDataType().getTypeName() == DataTypeName.DATE
                                && columnValue.getDataType().getTypeName() != DataTypeName.DATETIME) {
                                reason.add("字段请添加默认值！columnName : " + columnName);
                            }
                        }
                    }
                    // 所以字段必须添加NOT NULL
                    if (!columnValue.isNotNull()) {
                        reason.add("字段请设置为NOT NULL！columnName : " + columnName);
                    }
                    if (StringUtils.contains(columnName, "_")) {
                        if (!StringUtils.isAllLowerCase(StringUtils.replace(columnName, "_", ""))) {
                            reason.add("字段名不能包含大写！tableName : " + columnName);
                        }
                    } else {
                        reason.add("字段名必须包含_下划线分隔！tableName : " + columnName);
                    }
                }
                // 唯一索引检查
                for (Pair<Identifier, IndexDefinition> index : createTable.getUniqueKeys()) {
                    String indexName = index.getKey().getIdText();
                    if (!StringUtils.startsWith(indexName, "IUK_")) {
                        reason.add("唯一索引必须以IUK_开头!unique : " + indexName);
                    }
                }
                if (createTable.getKeys().size() >= 5) {
                    reason.add("单表索引不能超过 : " + createTable.getKeys().size() + "个");
                }
                // 索引检查
                for (Pair<Identifier, IndexDefinition> index : createTable.getKeys()) {
                    String indexName = index.getKey().getIdText();
                    if (!StringUtils.startsWith(indexName, "IDX_")) {
                        reason.add("索引必须以IDX_开头!key : " + indexName);
                    }
                }
            }
            if (reason.isEmpty()) {
                result.put(true, Arrays.asList("验证通过!" + sql));
                return result;
            } else {
                result.put(false, reason);
                return result;
            }
        } catch (SQLSyntaxErrorException e) {
            result.put(false, Arrays.asList(e.getMessage()));
            return result;
        }
    }

    public static void main(String[] args) {
        // String sql = " CREATE TABLE gggt_aa (" + "mapping_session_id int(10)
        // unsigned NOT NULL AUTO_INCREMENT,"
        // + "old_new_name char(85) NOT NULL DEFAULT ''," + "new_db_name
        // varchar(80) NOT NULL DEFAULT '',"
        // + "old_release varchar(5) NOT NULL DEFAULT ''," + "new_release
        // varchar(5) NOT NULL DEFAULT '',"
        // + "old_assembly varchar(20) NOT NULL DEFAULT '',"
        // + "new_assembly varchar(20) NOT NULL DEFAULT ''," + "create_time
        // datetime NOT NULL,"
        // + "PRIMARY KEY (mapping_session_id)," + "UNIQUE KEY IUK_abc
        // (mapping_session_id),"
        // + "KEY IDX_CC (s_num))";
        // String sql = "CREATE TABLE `tb_test` (`id` bigint(100) NOT NULL
        // AUTO_INCREMENT COMMENT '主键id' , `name` varchar(32) NOT NULL , PRIMARY
        // KEY (`id`), UNIQUE KEY `index`(`name`(6)) USING BTREE, CONSTRAINT
        // `id_pro` FOREIGN KEY (`id`) REFERENCES `travel_cinema_dev`.`tb_ad`
        // (`ADID`) ON DELETE RESTRICT ON UPDATE SET NULL) ENGINE=InnoDB DEFAULT
        // CHARACTER SET=utf8 COLLATE=utf8_unicode_ci COMMENT='测试'
        // AUTO_INCREMENT=1 ROW_FORMAT=COMPACT AVG_ROW_LENGTH=10 MAX_ROWS=10
        // MIN_ROWS=1 KEY_BLOCK_SIZE=10";
        // /*String sql = "ALTER TABLE table_name ADD PRIMARY KEY (id)";*/
        // System.out.println(SqlValidateHelper.validate(sql));
    }
}
